package com.gaoyi.crm.db.dao;

import android.text.TextUtils;

import androidx.room.Dao;
import androidx.room.RawQuery;
import androidx.sqlite.db.SimpleSQLiteQuery;

import com.gaoyi.crm.db.CrmDatabase;
import com.gaoyi.crm.db.dto.ShortDto;
import com.gaoyi.crm.db.dto.SyncedCustomerDto;
import com.gaoyi.crm.db.entity.Customer;
import com.gaoyi.crm.db.query.CtmSyncedQuery;

import java.util.List;

@Dao
public abstract class CustomerDao extends BaseDao<Customer> {

//    @Query("SELECT id,enName,syncedAt,syncedStatus FROM " + CrmDatabase.TABLE_CUSTOMER
//            + " WHERE (:keyword IS NULL OR :keyword ='' OR enName LIKE '%' || :keyword || '%') "
//            + " AND (:startDate = 0 OR syncedAt >= :startDate) "
//            + " AND (:endDate = 0 OR syncedAt <= :endDate) "
//            + " ORDER BY CASE WHEN :orderByDate = 0 THEN syncedAt END DESC,CASE WHEN :orderByDate = 1 THEN syncedAt END ASC "
//            + " ,CASE WHEN :orderByStatus = 0 THEN syncedStatus END DESC,CASE WHEN :orderByStatus = 1 THEN syncedStatus END ASC "
//            + " LIMIT :offset,:limit")
//    public abstract List<SyncedCustomerDto> getCustomer(String keyword, long startDate, long endDate, int orderByDate, int orderByStatus, int offset, int limit);

//    @Query("SELECT COUNT(id) FROM " + CrmDatabase.TABLE_CUSTOMER
//            + " WHERE (:keyword IS NULL OR :keyword ='' OR enName LIKE '%' || :keyword || '%') "
//            + " AND (:startDate = 0 OR syncedAt >= :startDate) "
//            + " AND (:endDate = 0 OR syncedAt <= :endDate) ")
//    public abstract int getCustomerCount(String keyword, long startDate, long endDate);

    @RawQuery
    public abstract List<SyncedCustomerDto> getCustomer(SimpleSQLiteQuery query);

    @RawQuery
    public abstract List<ShortDto> getShortCustomer(SimpleSQLiteQuery query);

    @RawQuery
    public abstract int getCustomerCount(SimpleSQLiteQuery query);

    public SimpleSQLiteQuery getCustomerSql(String nameType, CtmSyncedQuery query, int offset, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT id,").append(nameType).append(" AS name,syncedAt,syncedStatus FROM ").append(CrmDatabase.TABLE_CUSTOMER).append(" WHERE 1 = 1");
        if (!TextUtils.isEmpty(query.getKeyWord())) {
            sb.append(" AND name LIKE '%").append(query.getKeyWord()).append("%'");
        }
        if (query.getStartDate() != 0) {
            sb.append(" AND syncedAt >= ").append(query.getStartDate());
        }
        if (query.getEndDate() != 0) {
            sb.append(" AND syncedAt <= ").append(query.getEndDate());
        }
//        sb.append(" ORDER BY state ");
        boolean hasOrderBy = false;
        if (query.getOrderBySyncedAt() != null) {
            hasOrderBy  = true;
            sb.append(" ORDER BY syncedAt ").append(query.getOrderBySyncedAt() == 0 ? "DESC" : "ASC");
        }
        if (query.getOrderBySyncStatus() != null) {
            sb.append(hasOrderBy?" ,":" ORDER BY ");
            sb.append(" syncedStatus ").append(query.getOrderBySyncStatus() == 0 ? "DESC" : "ASC");
        }
        sb.append(" LIMIT ").append(offset).append(",").append(limit);
        return new SimpleSQLiteQuery(sb.toString());
    }

    public SimpleSQLiteQuery getCustomerSql(String nameType, CtmSyncedQuery query) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT COUNT(id) FROM ").append(CrmDatabase.TABLE_CUSTOMER).append(" WHERE 1 = 1");
        if (!TextUtils.isEmpty(query.getKeyWord())) {
            sb.append(" AND ").append(nameType).append(" LIKE '%").append(query.getKeyWord()).append("%'");
        }
        if (query.getStartDate() != 0) {
            sb.append(" AND syncedAt >= ").append(query.getStartDate());
        }
        if (query.getEndDate() != 0) {
            sb.append(" AND syncedAt <= ").append(query.getEndDate());
        }
        return new SimpleSQLiteQuery(sb.toString());
    }

    public SimpleSQLiteQuery getShortCustomerSql(String nameType) {
        return new SimpleSQLiteQuery("SELECT id," + nameType + " AS name FROM " + CrmDatabase.TABLE_CUSTOMER);
    }
}
